{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["Exercises","Excel functions","Circulation","Advertising sales","TV ratings","Miscellany"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":0,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Exercises","index":0,"n_rows":75,"n_cols":3,"rows_kept":75,"cut":false,"hidden":false,"merged":[[0,1,0,8]]}
[null,"Circulation"]
[]
[null,"As publication manager of a weekly magazine, you wish to develop guaranteed"]
[null,"circulation figures to use in soliciting advertising.  A study of weekly sales over the past"]
[null,"three years fails to reveal any marked trend or seasonal variation; the circulation"]
[null,"figures seem to be roughly normally distributed.  During the period studied, the mean"]
[null,"weekly circulation was 650,000 copies, with a sample standard deviation of 40,000"]
[null,"copies.  For parts (a)-(d), which review basic concepts involving random variables and "]
[null,"the normal distribution, assume these figures to be accurate estimates."]
[]
["(a)","If you guarantee the sale of at least 600,000 copies next week, what chance"]
[null,"will you face of failing to meet the guarantee?"]
["(b)","How many copies of your next issue must you print, in order to have only"]
[null,"one chance in 100 of being caught short?"]
["(c)","What mean weekly circulation figure would you be 95% safe in"]
[null,"guaranteeing over the next (52-week) year?"]
["(d)","What total circulation figure would you be 95% safe in"]
[null,"guaranteeing over the next (52-week) year?"]
["(e)","The estimate of mean weekly circulation is actually based on a"]
[null,"sample of 156 weeks  (3 years). Give a 95%-confidence interval"]
[null,"for the true mean weekly circulation."]
[]
[null,"Advertising Sales"]
[]
[null,"A magazine publishing house wishes to estimate (for purposes of advertising"]
[null,"sales) the average annual expenditure on furniture among its subscribers.  A"]
[null,"sample of 100 subscribers is chosen at random from the 100,000-person"]
[null,"subscription list, and each sampled subscriber is questioned about his"]
[null,"furniture purchases in the last year.  The sample mean response is $530,"]
[null,"with a sample standard deviation of $180.   "]
[]
["(a)","What estimate (of the population average) should be reported?"]
["(b)","Give a 95% confidence interval for the estimate."]
["(c)","How would your answer in (b) change, if the subscription list included 400,000 people?"]
["(d)","How would your answer in (b) change, if the subscription list included 1,000 people?"]
["(e)","How would your answer in (b) change, if the sample size were only 10?"]
["(f)","Did any of your answers above require special assumptions?"]
["(g)","If (b) had asked for a 99% confidence interval, would you have reported a"]
[null,"wider or narrower interval?"]
[]
[null,"TV Ratings"]
[]
[null,"The A.C. Nielsen Co. wishes to estimate, for each of several cities, the"]
[null,"amount of time a typical adult living in the city spends watching television"]
[null,"each week.  In San Diego (which has an adult population of 400,000), they"]
[null,"sample 100 people.  The sample mean is 25 hours, with a sample standard"]
[null,"deviation of 10 hours.  "]
[]
["(a)","Give a 95%-confidence interval for their estimate."]
["(b)","How many more people should they interview, in order to be 95%-confident"]
[null,"that their final estimate is wrong by no more than 1 hour?"]
["(c)","Assuming that viewing time varies from person to person in Los Angeles by"]
[null,"about as much as it does in San Diego, and that Nielsen wishes to make as"]
[null,"precise an estimate for L.A. as they did in part (a) for San Diego, how large"]
[null,"a sample should they take in L.A. (which has an adult population of"]
[null,"1,600,000)?"]
[]
[null,"Miscellany"]
[]
[null,"Explain, briefly, the difference between sampling bias and sampling error. "]
[]
[]
[null,"A 95%-confidence interval for an estimate indicates how much you can trust"]
[null,"the (fill in the blank) __________________ ."]
[]
[]
[null,"A firm has conducted a study, and has found that a 95%-confidence interval"]
[null,"for the mean time required to process new insurance policies is  (10 days,"]
[null,"12 days).  This means that         "]
[]
[null,"(a)","only 5 percent of all policies take less than 10 or more than 12 days to process."]
[null,"(b)","only 5 percent of all policies take between 10 and 12 days to process."]
[null,"(c)","about 95 out of every 100 intervals similarly constructed will contain the true"]
[null,null,"mean processing time.         "]
[null,"(d)","the probability is 0.95 that the true mean lies between 10 and 12 days."]

{"sheet":"Excel functions","index":1,"n_rows":49,"n_cols":14,"rows_kept":49,"cut":false,"hidden":false,"merged":[[0,1,0,7]]}
[null,"Excel Functions"]
[]
[null,"Excel's normal distribution and t-distribution functions are not consistently"]
[null,"defined. While NORMDIST and NORMINV are both left-tail oriented, TDIST is"]
[null,"right-tail oriented and TINV is two-tail oriented."]
[]
[null,1000,"mean"]
[null,200,"standard deviation"]
[null,900,"target"]
[null,{"$f":"NORMDIST(B9,B7,B8,TRUE)","v":0.30853753872598688},{"$f":"showformula(B10)","v":"=NORMDIST(B9,B7,B8,TRUE)"}]
[null,null,"Pr(normally-distributed random variable with given mean and"]
[null,null,"standard deviation is less than or equal to target value)"]
[]
[null,1000,"mean"]
[null,200,"standard deviation"]
[null,0.95,"target left-tail probability"]
[null,{"$f":"NORMINV(B16,B14,B15)","v":1328.9707253902943},{"$f":"showformula(B17)","v":"=NORMINV(B16,B14,B15)"}]
[null,null,"value that a normally-distributed random variable with given mean"]
[null,null,"and standard deviation has \"target probability\" of being less than"]
[null,null,"or equal to"]
[]
[null,9,"degrees of freedom",null,null,null,null,null,null,9,"degrees of freedom"]
[null,-1.5,"target value (must be non-negative)",null,null,null,null,null,null,1.5,"target value (must be non-negative)"]
[null,{"$f":"_xlfn.T.DIST(B23,B22,TRUE)","v":8.3925328028537471E-2},{"$f":"showformula(B24)","v":"=T.DIST(B23,B22,TRUE)"},null,null,"left tail",null,null,null,{"$f":"TDIST(J23,J22,TRUE)","v":8.3925328028537471E-2},{"$f":"showformula(J24)","v":"=TDIST(J23,J22,TRUE)"},null,null,"right tail"]
[null,null,"Pr(t-distributed random variable with given number of degrees of",null,null,null,null,null,null,null,"Pr(t-distributed random variable with given number of degrees of"]
[null,null,"freedom [and with mean 0 and standard deviation 1] is greater",null,null,null,null,null,null,null,"freedom [and with mean 0 and standard deviation 1] is greater"]
[null,null,"than or equal to target value)",null,null,null,null,null,null,null,"than or equal to target value)"]
[]
[null,9,null,null,null,null,null,null,null,9]
[null,2.5000000000000001E-2,"target two-tail probability",null,null,null,null,null,null,0.05,"target two-tail probability"]
[null,{"$f":"_xlfn.T.INV(B30,B29)","v":-2.2621571627982053},{"$f":"showformula(B31)","v":"=T.INV(B30,B29)"},null,null,"left tail",null,null,null,{"$f":"TINV(J30,J29)","v":2.2621571627982053},{"$f":"showformula(J31)","v":"=TINV(J30,J29)"},null,null,"two-tail"]
[null,null,"value that the absolute value of a t-distributed random variable with",null,null,null,null,null,null,null,"value that the absolute value of a t-distributed random variable with"]
[null,null,"given number of degrees of freedom has \"target probability\" of",null,null,null,null,null,null,null,"given number of degrees of freedom has \"target probability\" of"]
[null,null,"exceeding",null,null,null,null,null,null,null,"exceeding"]
[]
[null,"Excel offers functions for computing the variance and standard deviation of a"]
[null,"\"population\" of values, and a matching set of functions for using sample"]
[null,"values to estimate a population's variance and standard deviation."]
[]
[null,1]
[null,2,null,"a range of sample data"]
[null,3]
[null,4]
[]
[null,{"$f":"AVERAGE(B40:B43)","v":2.5},{"$f":"showformula(B45)","v":"=AVERAGE(B40:B43)"},null,null,"population and sample mean",null,null," sum of observations, divided by \"n\""]
[null,{"$f":"VARP(B40:B43)","v":1.25},{"$f":"showformula(B46)","v":"=VARP(B40:B43)"},null,null,"population variance",null,null," sum of squared differences from the mean, divided by \"n\""]
[null,{"$f":"STDEVP(B40:B43)","v":1.1180339887498949},{"$f":"showformula(B47)","v":"=STDEVP(B40:B43)"},null,null,"population standard deviation",null,null," square root of population variance"]
[null,{"$f":"VAR(B40:B43)","v":1.6666666666666667},{"$f":"showformula(B48)","v":"=VAR(B40:B43)"},null,null,"sample variance",null,null," sum of squared differences from the mean, divided by \"n-1\""]
[null,{"$f":"STDEV(B40:B43)","v":1.2909944487358056},{"$f":"showformula(B49)","v":"=STDEV(B40:B43)"},null,null,"sample standard deviation",null,null," square root of sample variance"]

{"sheet":"Circulation","index":2,"n_rows":70,"n_cols":6,"rows_kept":70,"cut":false,"hidden":false,"merged":[[0,1,0,7]]}
[null,"Circulation"]
[]
[null,"As publication manager of a weekly magazine, you wish to develop guaranteed"]
[null,"circulation figures to use in soliciting advertising.  A study of weekly sales over the past"]
[null,"three years fails to reveal any marked trend or seasonal variation; the circulation"]
[null,"figures seem to be roughly normally distributed.  During the period studied, the mean"]
[null,"weekly circulation was 650,000 copies, with a sample standard deviation of 40,000"]
[null,"copies.  For parts (a)-(d), which review basic concepts involving random variables and "]
[null,"the normal distribution, assume these figures to be accurate estimates."]
[]
["(a)","If you guarantee the sale of at least 600,000 copies next week, what chance"]
[null,"will you face of failing to meet the guarantee?"]
[]
[null,"You will only fail to meet the guarantee if demand runs below 600,000."]
[]
[null,650000,"weekly mean"]
[null,40000,"standard deviation"]
[null,600000,"guarantee",null,{"$f":"NORMDIST(B18,B16,B17,TRUE)","v":0.10564977366685525},"Pr(demand < guaranteed amount)"]
[]
["(b)","How many copies of your next issue must you print, in order to have only"]
[null,"one chance in 100 of being caught short?"]
[]
[null,"You will only run short if demand exceeds the number of copies printed."]
[]
[null,0.01,"chance of running out"]
[null,{"$f":"NORMINV(1-B25,B16,B17)","v":743053.91496163362},"number to print"]
[]
["(c)","What mean weekly circulation figure would you be 95% safe in"]
[null,"guaranteeing over the next (52-week) year?"]
[]
[null,"In a typical advertising contract, you promise to rebate some of the fees if your"]
[null,"circulation doesn't reach a specified target level. In this case, you want a 95%"]
[null,"chance that circulation will be above the guaranteed level, i.e., only a 5% chance"]
[null,"of circulation being below that level."]
[]
[null,"The key here is to remember that the variance of a sum of independent random"]
[null,"variables is the sum of their variances. Therefore the standard deviation of a"]
[null,"sum of  n  independent, identically-distributed random variables is sqrt(n) times"]
[null,"the standard deviation of a single one, and the standard deviation of the mean is"]
[null,"the original standard deviation divided by sqrt(n)."]
[]
[null,52,"time period"]
[null,{"$f":"B16","v":650000},"mean per week"]
[null,{"$f":"B17/SQRT(B42)","v":5547.0019622522914},"standard deviation of mean over time period"]
[null,0.05,"chance of failing to meet guarantee"]
[]
[null,{"$f":"NORMINV(B45,B43,B44)","v":640875.99370368244},"guarantee to make"]
[]
["(d)","What total circulation figure would you be 95% safe in"]
[null,"guaranteeing over the next (52-week) year?"]
[]
[null,"The answer obviously must be 52 times the answer to the previous question."]
[]
[null,{"$f":"B42*B47","v":33325551.672591485},"guarantee to make"]
[]
[null,"This could also be obtained directly."]
[]
[null,{"$f":"B42","v":52},"time period"]
[null,{"$f":"B16*B58","v":33800000},"expected total over time period"]
[null,{"$f":"B17*SQRT(B58)","v":288444.10203711916},"standard deviation of total over time period"]
[null,{"$f":"B45","v":0.05},"chance of failing to meet guarantee"]
[]
[null,{"$f":"NORMINV(B61,B59,B60)","v":33325551.672591485},"guarantee to make"]
[]
["(e)","The estimate of mean weekly circulation is actually based on a"]
[null,"sample of 156 weeks  (3 years). Give a 95%-confidence interval"]
[null,"for the true mean weekly circulation."]
[]
[null,{"$f":"B16","v":650000},"±",{"$f":"1.96*B17/SQRT(156)","v":6277.0236291594156},"fine for all practical purposes"]
[null,{"$f":"B16","v":650000},{"$f":"C69","v":"±"},{"$f":"TINV(0.05,156-1)*B17/SQRT(156)","v":6326.3018869236694},"a bit more precise"]

{"sheet":"Advertising sales","index":3,"n_rows":55,"n_cols":7,"rows_kept":55,"cut":false,"hidden":false,"merged":[[0,1,0,7]]}
[null,"Advertising Sales"]
[]
[null,"A magazine publishing house wishes to estimate (for purposes of advertising"]
[null,"sales) the average annual expenditure on furniture among its subscribers.  A"]
[null,"sample of 100 subscribers is chosen at random from the 100,000-person"]
[null,"subscription list, and each sampled subscriber is questioned about his"]
[null,"furniture purchases in the last year.  The sample mean response is $530,"]
[null,"with a sample standard deviation of $180.   "]
[]
["(a)","What estimate (of the population average) should be reported?"]
[]
[null,530,"sample mean, and estimate of (true) population mean"]
[null,180,"sample standard deviation, and estimate of population standard deviation"]
[null,100,"sample size"]
[]
["(b)","Give a 95% confidence interval for the estimate."]
[]
[null,{"$f":"B12","v":530},"±",{"$f":"1.96*B13/SQRT(B14)","v":35.28}]
[]
["(c)","How would your answer in (b) change, if the subscription list included 400,000 people?"]
[]
[null,"It wouldn't change. But see (d)."]
[]
["(d)","How would your answer in (b) change, if the subscription list included 1,000 people?"]
[]
[null,"Again, it wouldn't change. But this is assuming that the sample procedure"]
[null,"used simple random sampling with replacement. Had it used simple random"]
[null,"sampling without replacement, the answers to (b)-(d) would be:"]
[]
[null,null,"population size",null,"margin of error",null,"for sticklers"]
[null,null,100000,null,{"$f":"1.96*$B$13/SQRT($B$14)*SQRT((C31-$B$14)/(C31-1))","v":35.262531900893912},null,{"$f":"TINV(0.05,$B$14-1)*$B$13/SQRT($B$14)*SQRT((C31-$B$14)/(C31-1))","v":35.698221200821678}]
[null,null,400000,null,{"$f":"1.96*$B$13/SQRT($B$14)*SQRT((C32-$B$14)/(C32-1))","v":35.275633818910372},null,{"$f":"TINV(0.05,$B$14-1)*$B$13/SQRT($B$14)*SQRT((C32-$B$14)/(C32-1))","v":35.711485000733184}]
[null,null,1000,null,{"$f":"1.96*$B$13/SQRT($B$14)*SQRT((C33-$B$14)/(C33-1))","v":33.486294090148171},null,{"$f":"TINV(0.05,$B$14-1)*$B$13/SQRT($B$14)*SQRT((C33-$B$14)/(C33-1))","v":33.900036928306136}]
[]
["(e)","How would your answer in (b) change, if the sample size were only 10?"]
["(f)","Did any of your answers above require special assumptions?"]
[]
[null,"Assuming the the population distribution is roughly normal - probably a bad"]
[null,"assumption in reality, since it's likely that the population contains a number"]
[null,"of people who bought no furniture last year - the answer to (e) would be"]
[]
[null,{"$f":"B12","v":530},"±",{"$f":"B44*B13/SQRT(10)","v":128.76424307471967}]
[]
[null,{"$f":"TINV(0.05,10-1)","v":2.2621571627982053},"how many standard deviations out from the mean you must go in"]
[null,null,"the t-distribution with 10-1 = 9 degrees of freedom, in order to cut"]
[null,null,"off 5% of the distribution in the two tails"]
[]
[]
["(g)","If (b) had asked for a 99% confidence interval, would you have reported a"]
[null,"wider or narrower interval?"]
[]
[null,"Wider. The 99%-confidence interval is"]
[]
[null,{"$f":"B12","v":530},"±",{"$f":"NORMINV(0.995,0,1)*B13/SQRT(B14)","v":46.3649274638802},"fine"]
[null,{"$f":"B12","v":530},"±",{"$f":"TINV(0.01,100-1)*B13/SQRT(B14)","v":47.275298231054904},"a bit more precise"]

{"sheet":"TV ratings","index":4,"n_rows":32,"n_cols":4,"rows_kept":32,"cut":false,"hidden":false,"merged":[[0,1,0,7]]}
[null,"TV Ratings"]
[]
[null,"The A.C. Nielsen Co. wishes to estimate, for each of several cities, the"]
[null,"amount of time a typical adult living in the city spends watching television"]
[null,"each week.  In San Diego (which has an adult population of 400,000), they"]
[null,"sample 100 people.  The sample mean is 25 hours, with a sample standard"]
[null,"deviation of 10 hours.  "]
[]
["(a)","Give a 95%-confidence interval for their estimate."]
[]
[null,25,"±",{"$f":"1.96*10/SQRT(100)","v":1.9600000000000002}]
[]
["(b)","How many more people should they interview, in order to be 95%-confident"]
[null,"that their final estimate is wrong by no more than 1 hour?"]
[]
[null,"It takes roughly four times as large a sample, in order to cut the margin"]
[null,"of error in the estimate in half (this is the \"square-root\" effect). Hence, they"]
[null,"need a total sample of roughly 400 viewers."]
[]
[null,"Working it out precisely, the total should be"]
[]
[null,{"$f":"(1.96*10/1)^2","v":384.16000000000008},"viewers in the full sample"]
[]
["(c)","Assuming that viewing time varies from person to person in Los Angeles by"]
[null,"about as much as it does in San Diego, and that Nielsen wishes to make as"]
[null,"precise an estimate for L.A. as they did in part (a) for San Diego, how large"]
[null,"a sample should they take in L.A. (which has an adult population of"]
[null,"1,600,000)?"]
[]
[null,"The same size. For practical purposes (i.e., ignoring the distinction between"]
[null,"sampling with and without replacement), the margin of error in an estimate"]
[null,"depends on the sample size, but not on the population size."]

{"sheet":"Miscellany","index":5,"n_rows":41,"n_cols":2,"rows_kept":41,"cut":false,"hidden":false,"merged":[[0,1,0,7]]}
[null,"Miscellany"]
[]
[null,"Explain, briefly, the difference between sampling bias and sampling error. "]
[]
[null,"Sampling bias is the result of being dumb; its presence is undetectable by"]
[null,"statistical analysis; it is completely avoidable through the use of good"]
[null,"judgment.  Sampling error is the result of being unlucky; our exposure to it"]
[null,"can be measured and controlled; it can never be eliminated (except through"]
[null,"100% sampling)."]
[]
[]
[null,"A 95%-confidence interval for an estimate indicates how much you can trust"]
[null,"the (fill in the blank) __________________ ."]
[]
[null,"... estimation procedure. The point here is that we don't have any idea how"]
[null,"accurate our actual estimate is, but we do know how much accuracy is"]
[null,"inherent in the procedure we used to make the estimate."]
[]
[]
[null,"A firm has conducted a study, and has found that a 95%-confidence interval"]
[null,"for the mean time required to process new insurance policies is  (10 days,"]
[null,"12 days).  This means that         "]
[]
["(a)","only 5 percent of all policies take less than 10 or more than 12 days to process."]
["(b)","only 5 percent of all policies take between 10 and 12 days to process."]
["(c)","about 95 out of every 100 intervals similarly constructed will contain the true"]
[null,"mean processing time.         "]
["(d)","the probability is 0.95 that the true mean lies between 10 and 12 days."]
[]
[null,"(a) and (b) are totally wrong, since they refer to the distribution of individuals"]
[null,"within the population, and a 95%-confidence interval tells us nothing about"]
[null,"that. It could well be that half of all the policies are approved instantly, and"]
[null,"the other half take about three weeks."]
[]
[null,"(c) is objectively (no one could argue) correct. It restates the definition of a"]
[null,"95%-confidence interval."]
[]
[null,"(d) is subjectively (from the viewpoint of the person making the estimate, "]
[null,"who sees only this sample data) correct, if we accept the principle that"]
[null,"most people equate lack-of-personal-knowledge with randomness. (d) is the"]
[null,"interpretation most commonly used in practice."]